﻿/*
{
"状态":1,
"名称":"获取-权限信息",
"作者":"陈泽椿",
"创建时间":"2018-06-30",
"功能":"获取-权限信息",
"log": [
	{"date":"06-01" , "dev" :"hzhh" , "content" : ""}
]
}
*/
declare @sql varchar(max)
declare @power varchar(100),@classify varchar(100)
declare @where varchar(max)=''
declare @power_tree_key int
 
set @power = '{power_id}'
set @classify = '{classify}'
set @power_tree_key='{power_tree_key}'

if isnull(@power_tree_key,'')<>''
begin 
	set @where = '  And power_tree_key in (select power_tree_key from #T)'
end 

if (@classify <>'')
begin
	set @where += ' and classify='''+@classify+''''
end

if (@power <>'')
begin
	set @where += ' and (power_id like ''%'+@power+'%'' or power_name like ''%'+@power+'%'')'
end
set @sql = '
;WITH CT 
AS
(
	SELECT power_tree_key,father_key FROM y_power_tree WHERE power_tree_key='+convert(varchar(10),@power_tree_key)+'
	UNION ALL
    SELECT A.power_tree_key,A.father_key FROM CT INNER JOIN y_power_tree A ON CT.power_tree_key=A.father_key
)
SELECT power_tree_key into #T FROM CT

select power_key,power_id,power_name,remarks,classify,detail_name as name,convert(varchar(20),y_power.create_time,120)create_time,
convert(varchar(20),y_power.update_time,120)update_time,content from y_power,y_dict where 1=1 and classify=dict_id  {where} '
set @sql =replace(@sql,'{where}',@where)

exec(@sql)